-- 插入示例数据
USE ParkingManagement;
GO

-- 插入停车场信息
INSERT INTO ParkingLot (ParkingLotName, ParkingLotType, TotalParkingSpaces, Address, ContactPhone)
VALUES 
('城市中心停车场', '商业停车场', 500, '城市中心区商业街88号', '13800138001'),
('阳光医院停车场', '医院', 300, '健康路123号', '13800138002'),
('翠湖公园停车场', '风景区', 200, '翠湖路45号', '13800138003'),
('明德大学停车场', '学校', 400, '教育大道100号', '13800138004');

-- 插入道闸设备信息
INSERT INTO GateDevice (ParkingLotID, GateType, GateLocation, LastMaintenanceDate, InstallationDate, DeviceStatus)
VALUES 
(1, 'Entry', '北门入口', '2024-02-15', '2023-05-10', 'Active'),
(1, 'Exit', '北门出口', '2024-02-15', '2023-05-10', 'Active'),
(1, 'Entry', '南门入口', '2024-02-20', '2023-05-12', 'Active'),
(1, 'Exit', '南门出口', '2024-02-20', '2023-05-12', 'Active'),
(2, 'Entry', '主入口', '2024-01-10', '2023-04-05', 'Active'),
(2, 'Exit', '主出口', '2024-01-10', '2023-04-05', 'Active'),
(3, 'Both', '公园门口', '2024-03-01', '2023-06-20', 'Active'),
(4, 'Entry', '东门入口', '2024-01-25', '2023-03-15', 'Active'),
(4, 'Exit', '东门出口', '2024-01-25', '2023-03-15', 'Active');

-- 插入收费标准
INSERT INTO PricingRules (ParkingLotID, VehicleType, BasePricePerHour, MaximumDailyFee, SpecialRules, EffectiveFrom, EffectiveTo)
VALUES 
(1, '小型车', 10.00, 80.00, '前30分钟免费', '2023-01-01', NULL),
(1, '大型车', 20.00, 160.00, '不享受优惠', '2023-01-01', NULL),
(2, '小型车', 8.00, 50.00, '看病患者凭证首小时免费', '2023-01-01', NULL),
(2, '大型车', 15.00, 100.00, '不享受优惠', '2023-01-01', NULL),
(3, '小型车', 15.00, 120.00, '工作日9折', '2023-01-01', NULL),
(3, '大型车', 30.00, 200.00, '不享受优惠', '2023-01-01', NULL),
(4, '小型车', 5.00, 40.00, '教职工半价', '2023-01-01', NULL),
(4, '大型车', 10.00, 80.00, '教职工7折', '2023-01-01', NULL);

-- 插入会员信息
INSERT INTO Members (VehicleNumber, OwnerName, MembershipType, RegistrationDate, ExpiryDate, ContactPhone, Balance, ParkingLotID)
VALUES 
('京A12345', '张三', '金卡', '2023-06-01', '2024-06-01', '13900001111', 500.00, 1),
('京B54321', '李四', '银卡', '2023-07-15', '2024-07-15', '13900002222', 300.00, 1),
('京C98765', '王五', '普通', '2023-08-20', '2024-08-20', '13900003333', 100.00, 2),
('京D56789', '赵六', '金卡', '2023-05-10', '2024-05-10', '13900004444', 800.00, 3),
('京E13579', '钱七', '银卡', '2023-09-01', '2024-09-01', '13900005555', 250.00, 4);

-- 插入车辆进出记录（过去一周的数据）
-- 当前日期减去7天开始插入
DECLARE @StartDate DATETIME = DATEADD(DAY, -7, GETDATE());
DECLARE @CurrentDate DATETIME = @StartDate;
DECLARE @EndDate DATETIME = GETDATE();

-- 创建临时表存储车牌号
CREATE TABLE #TempVehicleNumbers (
    ID INT IDENTITY(1,1),
    VehicleNumber NVARCHAR(20)
);

-- 插入一些车牌号
INSERT INTO #TempVehicleNumbers (VehicleNumber)
VALUES 
('京A12345'), ('京B54321'), ('京C98765'), ('京D56789'), ('京E13579'),
('京F24680'), ('京G13579'), ('京H97531'), ('京J46802'), ('京K13579'),
('沪A12345'), ('沪B54321'), ('沪C98765'), ('沪D56789'), ('沪E13579'),
('粤A12345'), ('粤B54321'), ('粤C98765'), ('粤D56789'), ('粤E13579');

-- 循环插入每天的数据
WHILE @CurrentDate <= @EndDate
BEGIN
    -- 为每个停车场每天插入20-50条随机进出记录
    DECLARE @ParkingLotID INT = 1;
    
    WHILE @ParkingLotID <= 4
    BEGIN
        -- 获取此停车场的入口和出口道闸ID
        DECLARE @EntryGateID INT, @ExitGateID INT;
        
        SELECT TOP 1 @EntryGateID = GateID 
        FROM GateDevice 
        WHERE ParkingLotID = @ParkingLotID AND (GateType = 'Entry' OR GateType = 'Both');
        
        SELECT TOP 1 @ExitGateID = GateID 
        FROM GateDevice 
        WHERE ParkingLotID = @ParkingLotID AND (GateType = 'Exit' OR GateType = 'Both');
        
        -- 决定今天插入多少条记录
        DECLARE @RecordsToInsert INT = FLOOR(RAND() * 31) + 20; -- 20到50之间的随机数
        DECLARE @Counter INT = 0;
        
        WHILE @Counter < @RecordsToInsert
        BEGIN
            -- 随机车牌
            DECLARE @RandomVehicleID INT = FLOOR(RAND() * 20) + 1;
            DECLARE @VehicleNumber NVARCHAR(20);
            
            SELECT @VehicleNumber = VehicleNumber 
            FROM #TempVehicleNumbers 
            WHERE ID = @RandomVehicleID;
            
            -- 随机进入时间（当前日期的随机时间）
            DECLARE @EntryTime DATETIME = DATEADD(MINUTE, FLOOR(RAND() * 1440), @CurrentDate);
            
            -- 随机停留时间（30分钟到5小时）
            DECLARE @StayMinutes INT = FLOOR(RAND() * 270) + 30;
            DECLARE @ExitTime DATETIME = DATEADD(MINUTE, @StayMinutes, @EntryTime);
            
            -- 如果出场时间超过当前日期，有20%概率设为NULL（表示尚未离场）
            IF @ExitTime > @EndDate OR (RAND() < 0.2 AND @CurrentDate = @EndDate)
                SET @ExitTime = NULL;
            
            -- 计算费用（如果已经离场）
            DECLARE @PaymentAmount DECIMAL(10,2) = NULL;
            DECLARE @PaymentStatus NVARCHAR(20) = NULL;
            DECLARE @VehicleType NVARCHAR(20) = CASE WHEN RAND() < 0.8 THEN '小型车' ELSE '大型车' END;
            
            IF @ExitTime IS NOT NULL
            BEGIN
                -- 获取收费标准
                DECLARE @BasePricePerHour DECIMAL(10,2);
                DECLARE @MaximumDailyFee DECIMAL(10,2);
                
                SELECT @BasePricePerHour = BasePricePerHour, @MaximumDailyFee = MaximumDailyFee
                FROM PricingRules
                WHERE ParkingLotID = @ParkingLotID AND VehicleType = @VehicleType AND 
                      @EntryTime BETWEEN EffectiveFrom AND ISNULL(EffectiveTo, '9999-12-31');
                
                -- 计算费用
                DECLARE @Hours DECIMAL(10,2) = CEILING(DATEDIFF(MINUTE, @EntryTime, @ExitTime) / 60.0);
                SET @PaymentAmount = CASE 
                                       WHEN @Hours * @BasePricePerHour > @MaximumDailyFee 
                                       THEN @MaximumDailyFee 
                                       ELSE @Hours * @BasePricePerHour 
                                     END;
                
                -- 95%概率已支付，5%概率未支付
                SET @PaymentStatus = CASE WHEN RAND() < 0.95 THEN '已支付' ELSE '未支付' END;
            END
            
            -- 插入进出记录
            INSERT INTO VehicleEntryExit (
                ParkingLotID, VehicleNumber, EntryTime, ExitTime, 
                PaymentAmount, PaymentStatus, GateEntryID, GateExitID, VehicleType
            )
            VALUES (
                @ParkingLotID, @VehicleNumber, @EntryTime, @ExitTime,
                @PaymentAmount, @PaymentStatus, @EntryGateID, 
                CASE WHEN @ExitTime IS NULL THEN NULL ELSE @ExitGateID END,
                @VehicleType
            );
            
            SET @Counter = @Counter + 1;
        END
        
        SET @ParkingLotID = @ParkingLotID + 1;
    END
    
    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
END

-- 插入一些设备故障记录
INSERT INTO DeviceErrors (GateID, ErrorTime, ErrorDescription, ResolutionTime, Resolution)
VALUES 
(1, DATEADD(DAY, -5, GETDATE()), '道闸无法正常抬起', DATEADD(DAY, -5, GETDATE()), '重置系统后恢复正常'),
(3, DATEADD(DAY, -3, GETDATE()), '摄像头无法识别车牌', DATEADD(DAY, -3, GETDATE()), '清洁摄像头并重新校准'),
(5, DATEADD(DAY, -2, GETDATE()), '支付系统连接失败', DATEADD(DAY, -2, GETDATE()), '重启网络设备后恢复'),
(7, DATEADD(DAY, -1, GETDATE()), '显示屏黑屏', NULL, NULL); -- 未解决的故障

-- 清理临时表
DROP TABLE #TempVehicleNumbers;

GO 